﻿using Dapper;
using Domain.Entity;
using Domain.Entity.ord;
using Domain.IRepository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class PurchaseReturn_hRepository : IPurchaseReturn_hRepository
    {
        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";


        //添加进货退货单
        public async Task<int> AddPurchaseReturn_h(PurchaseReturn_h purchaseReturn_H)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"insert into [dbo].[ord_purchase_return_h](purt_code, purt_date, pur_id, pur_code, sup_id, sup_name, sup_linkman, sup_linkphone, pur_depId, pur_depName, pur_userId, pur_userName, purt_reason, purt_categories, purt_num, purt_total, purt_outstockOK_num, purt_outstockNO_num, purt_status, create_name, create_time, remark)
                                values('{purchaseReturn_H.purt_code}','{purchaseReturn_H.purt_date}',{purchaseReturn_H.pur_id},'{purchaseReturn_H.pur_code}',{purchaseReturn_H.sup_id},'{purchaseReturn_H.sup_name}','{purchaseReturn_H.sup_linkman}','{purchaseReturn_H.sup_linkphone}',{purchaseReturn_H.pur_depId},'{purchaseReturn_H.pur_depName}',{purchaseReturn_H.pur_userId},'{purchaseReturn_H.pur_userName}','{purchaseReturn_H.purt_reason}',{purchaseReturn_H.purt_categories},{purchaseReturn_H.purt_num},'{purchaseReturn_H.purt_total}',{purchaseReturn_H.purt_outstockOK_num},{purchaseReturn_H.purt_outstockNO_num},{purchaseReturn_H.purt_status},'{purchaseReturn_H.create_name}','{purchaseReturn_H.create_time}','{purchaseReturn_H.remark}');
                                SELECT CAST(SCOPE_IDENTITY() AS INT);";
                return await sqlConnection.ExecuteScalarAsync<int>(sql);
            }
        }

        //审核
        public async Task<int> ApproverPurchaseReturn_h(int purtid, int status, string approvername, string approvertime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[ord_purchase_return_h] set purt_status={status},approver_name='{approvername}',approver_time='{approvertime}' where purt_id={purtid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //删除
        public async Task<int> DeletePurchaseReturn_h(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[ord_purchase_return_h] set is_delete=1 where purt_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //高级查询
        public async Task<FenYe<PurchaseReturn_h>> GetAdvancedPurchaseReturn_h(FenYe<PurchaseReturn_h> fenYe, string? code, string? starttotal, string? endtotal, string? startdate, string? enddate, string? startnum, string? endnum, int? status, int? sup, string? suplink, string? purcode, int? dep, int? user, string? createname, string? startcreatetime, string? endcreatetime, string? approvername, string? startapprovertime, string? endapprovertime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "create_time DESC");
                param.Add("@tabelName", "vw_purchasereturn");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 ";
                if (!string.IsNullOrEmpty(code))
                    sqlparam += $" and purt_code like '%{code}%'";
                if (!string.IsNullOrEmpty(starttotal) || !string.IsNullOrEmpty(endtotal))
                    sqlparam += $" and  purt_total between '{starttotal}' and '{endtotal}'";
                if (!string.IsNullOrEmpty(startdate) || !string.IsNullOrEmpty(enddate))
                    sqlparam += $" and  purt_date between '{startdate}' and '{enddate}'";
                if (!string.IsNullOrEmpty(startnum) || !string.IsNullOrEmpty(endnum))
                    sqlparam += $" and  purt_num between '{startnum}' and '{endnum}'";
                if (status != null && status != 0)
                    sqlparam += $" and  purt_status={status}";
                if (sup != null && sup != 0)
                    sqlparam += $" and  sup_id={sup}";
                if (!string.IsNullOrEmpty(suplink))
                    sqlparam += $" and sup_linkman like '%{suplink}%'";
                if (!string.IsNullOrEmpty(purcode))
                    sqlparam += $" and pur_code like '%{purcode}%'";
                if (dep != null && dep != 0)
                    sqlparam += $" and  pur_depId={dep}";
                if (user != null && user != 0)
                    sqlparam += $" and  pur_userId={user}";
                if (!string.IsNullOrEmpty(createname))
                    sqlparam += $" and create_name like '%{createname}%'";
                if (!string.IsNullOrEmpty(startcreatetime) || !string.IsNullOrEmpty(endcreatetime))
                    sqlparam += $" and  create_time between '{startcreatetime}' and '{endcreatetime}'";
                if (!string.IsNullOrEmpty(approvername))
                    sqlparam += $" and approver_name like '%{approvername}%'";
                if (!string.IsNullOrEmpty(startapprovertime) || !string.IsNullOrEmpty(endapprovertime))
                    sqlparam += $" and  approver_time between '{startapprovertime}' and '{endapprovertime}'";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<PurchaseReturn_h> list = sqlConnection.Query<PurchaseReturn_h>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }


        //查询所有单据
        public async Task<List<PurchaseReturn_h>> GetAllPurchaseReturn_h()
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = "select * from [dbo].[ord_purchase_return_h] where is_delete=0 and (purt_status=7 or purt_status=8)";
                return sqlConnection.Query<PurchaseReturn_h>(sql).ToList();
            }
        }


        //分页查询
        public async Task<FenYe<PurchaseReturn_h>> GetPurchaseReturn_h(FenYe<PurchaseReturn_h> fenYe, string? code, int? status, int? sup, string? starttime, string? endtime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "create_time DESC");
                param.Add("@tabelName", "vw_purchasereturn");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 ";
                if (!string.IsNullOrEmpty(code))
                    sqlparam += $" and purt_code like '%{code}%'";
                if (sup != 0 && sup != null)
                    sqlparam += $" and sup_id ={sup}";
                if (status != null && status != 0)
                    sqlparam += $" and  purt_status={status}";
                if (!string.IsNullOrEmpty(starttime) || !string.IsNullOrEmpty(endtime))
                    sqlparam += $" and  purt_date between '{starttime}' and '{endtime}'";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<PurchaseReturn_h> list = sqlConnection.Query<PurchaseReturn_h>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }


        //根据编号查询信息
        public async Task<PurchaseReturn_h> GetPurchaseReturn_hByCode(string code)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[ord_purchase_return_h] where purt_code='{code}'";
                return await sqlConnection.QueryFirstAsync<PurchaseReturn_h>(sql);
            }
        }


        //根据id查询信息
        public async Task<PurchaseReturn_h> GetPurchaseReturn_hById(int purtId)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[ord_purchase_return_h] where purt_id={purtId}";
                return await sqlConnection.QueryFirstAsync<PurchaseReturn_h>(sql);
            }
        }

        //根据关联单号查询退货记录
        public async Task<List<PurchaseReturn_h>> GetPurchaseReturn_hs(string purcode)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[ord_purchase_return_h] where pur_code='{purcode}'";
                return sqlConnection.Query<PurchaseReturn_h>(sql).ToList();
            }
        }

        //更新出库数及状态
        public async Task<int> UpdateNum(int purtid, int oknum, int nonum, int statu)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ord_purchase_return_h] set purt_outstockOK_num={oknum},purt_outstockNO_num={nonum},purt_status={statu} where purt_id={purtid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //修改
        public async Task<int> UpdatePurchaseReturn_h(PurchaseReturn_h purchaseReturn_H)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ord_purchase_return_h] set purt_date='{purchaseReturn_H.purt_date}',pur_id={purchaseReturn_H.pur_id},pur_code='{purchaseReturn_H.pur_code}',sup_id={purchaseReturn_H.sup_id},sup_name='{purchaseReturn_H.sup_name}',sup_linkman='{purchaseReturn_H.sup_linkman}',sup_linkphone='{purchaseReturn_H.sup_linkphone}',
                                pur_depId={purchaseReturn_H.pur_depId},pur_depName='{purchaseReturn_H.pur_depName}',pur_userId={purchaseReturn_H.pur_userId},pur_userName='{purchaseReturn_H.pur_userName}',purt_reason='{purchaseReturn_H.purt_reason}',purt_categories={purchaseReturn_H.purt_categories},purt_num={purchaseReturn_H.purt_num},purt_total='{purchaseReturn_H.purt_total}',
                                purt_outstockOK_num={purchaseReturn_H.purt_outstockOK_num},purt_outstockNO_num={purchaseReturn_H.purt_outstockNO_num},purt_status={purchaseReturn_H.purt_status},update_name='{purchaseReturn_H.update_name}',update_time='{purchaseReturn_H.update_time}',remark='{purchaseReturn_H.remark}'  where purt_id={purchaseReturn_H.purt_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //撤销
        public async Task<int> UpdateStatus(int purtid, int status)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ord_purchase_return_h] set purt_status={status} where purt_id={purtid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }
    }
}
